package inet.db.tennis;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import inet.db.pool.*;
import inet.util.Logger;

public class TennisMatchGameDAO {

	private Logger logger = null;
	private DBPoolX poolX = null;
	public TennisMatchGameDAO(){
		logger = new Logger(this.getClass().getName());
		try{
			poolX = DBPoolX.getInstance(DBPoolXName.SERVICE_BONGDA);
		}catch(Exception exception){
			logger.log(exception.toString()); 
		}
	}
	
    /**
     * function get a tennis match game
     * @param id
     * @return TennisMatchGame
     */
    public TennisMatchGame getRow(BigDecimal id) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisMatchGame match = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select ID, MATCH_ID, MATCH_SET, POINT1, POINT2, SERVE, TYPE, SPECIAL_POINT, GEN_DATE " +
    			" from TENNIS_MATCH_GAME where ID = ? ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, id);
    		
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			match = new TennisMatchGame();
    			match.setId(rs.getBigDecimal(1));
    			match.setMatchId(rs.getBigDecimal(2));
    			match.setMatchSet(rs.getInt(3));
    			match.setPoint1(rs.getString(4));
    			match.setPoint2(rs.getString(5));
    			match.setServe(rs.getInt(6));
    			match.setType(rs.getInt(7));
    			match.setSpecialPoint(rs.getInt(8));
    			match.setGenDate(rs.getTimestamp(9));
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return match;
    }
    
    /**
     * function get a newest record tennis match game of match
     * @param matchId
     * @return TennisMatchGame
     */
    public TennisMatchGame getNewestRows(BigDecimal matchId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisMatchGame match = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select ID, MATCH_ID, MATCH_SET, POINT1, POINT2, SERVE, TYPE, SPECIAL_POINT, GEN_DATE " +
    			" from TENNIS_MATCH_GAME where MATCH_ID = ? and rownum = 1 order by ID desc ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, matchId);
    		
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			match = new TennisMatchGame();
    			match.setId(rs.getBigDecimal(1));
    			match.setMatchId(rs.getBigDecimal(2));
    			match.setMatchSet(rs.getInt(3));
    			match.setPoint1(rs.getString(4));
    			match.setPoint2(rs.getString(5));
    			match.setServe(rs.getInt(6));
    			match.setType(rs.getInt(7));
    			match.setSpecialPoint(rs.getInt(8));
    			match.setGenDate(rs.getTimestamp(9));
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getNewestRows: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return match;
    }
    
    /**
     * function get match game 
     * @param matchId
     * @return Vector<TennisMatchGame>
     */
    public Vector<TennisMatchGame> findAll(BigDecimal matchId) {
    	if (matchId == null) return null;

    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisMatchGame match = null;
    	Vector <TennisMatchGame> vMatch = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select ID, MATCH_ID, MATCH_SET, POINT1, POINT2, SERVE, TYPE, SPECIAL_POINT, GEN_DATE " +
    			" from TENNIS_MATCH_GAME where MATCH_ID = ? ";
    		
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, matchId);
    		
    		rs = preStmt.executeQuery();
    		vMatch = new Vector<TennisMatchGame>();
    		while (rs.next()) {
    			match = new TennisMatchGame();
    			match.setId(rs.getBigDecimal(1));
    			match.setMatchId(rs.getBigDecimal(2));
    			match.setMatchSet(rs.getInt(3));
    			match.setPoint1(rs.getString(4));
    			match.setPoint2(rs.getString(5));
    			match.setServe(rs.getInt(6));
    			match.setType(rs.getInt(7));
    			match.setSpecialPoint(rs.getInt(8));
    			match.setGenDate(rs.getTimestamp(9));
    			
    			vMatch.addElement(match);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vMatch;
    }
	
    /**
     * function insert a tennis match
     * @param matchId
     * @param matchSet
     * @param point1
     * @param point2
     * @param serve
     * @param type
     * @param specialPoint
     * @return true if insert success else return false.
     */
	public boolean insertRow(BigDecimal matchId, int matchSet, String point1, String point2, int serve, int type, int specialPoint){
		if (matchId == null) {
    		return false;
    	}
		Connection conn = null;
        PreparedStatement preStmt = null;
        String strSQL = null;
        boolean result = false;
    	
        try {
            conn = poolX.getConnection();

			strSQL =
    			" insert into TENNIS_MATCH_GAME(ID, MATCH_ID, MATCH_SET, POINT1, POINT2, SERVE, TYPE, SPECIAL_POINT, GEN_DATE) " +
    			" values (TENNIS_MATCH_GAME_SEQ.nextval(), ?, ?, ?, ?, ?, ?, ?, sysdate)";
	        preStmt = conn.prepareStatement(strSQL);
	        preStmt.setBigDecimal(1, matchId);
	        preStmt.setInt(2, matchSet);
	        preStmt.setString(3, point1);
	        preStmt.setString(4, point2);
	        preStmt.setInt(5, serve);
	        preStmt.setInt(6, type);
	        preStmt.setInt(7, specialPoint);
	        
	        if (preStmt.executeUpdate()>=1) {
	        	result = false;
	        }
        } catch (Exception ex) {
        	logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
       return result;
	}
	
	/**
	 * function update a tennis match game 
	 * @param id
	 * @param matchSet
	 * @param point1
	 * @param point2
	 * @param serve
	 * @param type
	 * @param specialPoint
	 * @return true if update successfull else return false.
	 */
	public boolean updateRow(BigDecimal id, int matchSet, String point1, String point2, int serve, int type, int specialPoint){
		if (id == null ) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSQL = null;
		boolean result = false;
		ResultSet rs = null;
		
		try {
			conn = poolX.getConnection();
			strSQL =
				" update TENNIS_MATCH_GAME set MATCH_SET = ?, POINT1 = ?, POINT2 = ?, SERVE = ?, TYPE = ?, SPECIAL_POINT = ? " +
				" where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, matchSet);
	        preStmt.setString(2, point1);
	        preStmt.setString(3, point2);
	        preStmt.setInt(4, serve);
	        preStmt.setInt(5, type);
	        preStmt.setInt(6, specialPoint);
			preStmt.setBigDecimal(7, id);

			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt, rs);
		}
		return result;
	}
	
	/**
	 * function delete a TennisMatchGame
	 * @param id
	 * @return true if update success else return false.
	 */
	public boolean deleteRow(BigDecimal id){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_MATCH_GAME where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
}
